package com.turing.post.common.impl;

import java.util.Date;
import java.util.HashMap;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import com.turing.post.common.util.ContrastDao;
import com.turing.post.util.PCPSUtil;

@Stateless(mappedName = "common.impl.ContrastDaoImpl")
@Remote
@Local
public class ContrastDaoImpl implements ContrastDao {

	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	/**
	 * 按照分页获取激活卡序号,插入map表中
	 */
	@SuppressWarnings("unchecked")
	public String getMapIssueBranchByNo(String branchno) {
		if (PCPSUtil.isEmpty(branchno))
			return null;
		try {
			String sql = "select IssueBranchNo from MapIssueBranch where BranchNo=?1";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, branchno);
			return query.getSingleResult().toString();
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 根据旧数据库机构号，查询新数据库中的销售机构号
	 */
	public String getMapSaleBranchByNo(String branchno) {
		if (PCPSUtil.isEmpty(branchno))
			return null;
		try {
			String sql = "select SaleBranchNo from MapSaleBranch where Customid=?1";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, branchno);
			return query.getSingleResult().toString();
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 根据旧数据库机构号，查询新数据库中的商户号
	 */
	public String getMapMerchantByNo(String branchno) {
		if (PCPSUtil.isEmpty(branchno))
			return null;
		try {
			String sql = "select newMerchantno from MapMerchant where Merchantno=?1";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, branchno);
			return query.getSingleResult().toString();
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 得到新旧销售机构对应的map
	 */
	@SuppressWarnings("unchecked")
	public HashMap<String, String> getMapSaleBranch() {
		HashMap<String, String> salebranch = new HashMap();
		String sql = "select customid,salebranchno from MapSaleBranch";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> mlist = query.getResultList();
		for (Object[] o : mlist) {
			salebranch.put(o[0].toString(), o[1].toString());
		}
		return salebranch;
	}

	/**
	 * 得到新旧商户对应的map
	 */
	@SuppressWarnings("unchecked")
	public HashMap<String, String> getMapMerchant() {
		HashMap<String, String> MapMerchant = new HashMap();
		String sql = "select merchantno,newMerchantno from MapMerchant";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> mlist = query.getResultList();
		for (Object[] o : mlist) {
			MapMerchant.put(o[0].toString(), o[1].toString());
		}
		return MapMerchant;
	}

	/**
	 * 根据旧数据库机构号，查询新数据库中的卡类别
	 */
	public String getMapCardClassicByNo(String id) {
		if (PCPSUtil.isEmpty(id))
			return null;
		try {
			String sql = "select Cardclassicid from MapCardClassic where BINDINGTYPENO=?1";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, id);
			return query.getSingleResult().toString();
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 根据旧数据库机构号，查询新数据库中的卡类别面值
	 */
	public String getMapCardParvalueByNo(String id) {
		if (PCPSUtil.isEmpty(id))
			return null;
		try {
			String sql = "select CardParValueId from MapCardParvalue where valueno=?1";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, id);
			return query.getSingleResult().toString();
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 根据旧数据库机构号，查询新数据库中的用户
	 */
	public String getMapSysOperuserByNo(String id) {
		if (PCPSUtil.isEmpty(id))
			return null;
		try {
			String sql = "select OperId from MapSysOperuser where Operno=?1";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, id);
			return query.getSingleResult().toString();
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 插入商户对应表
	 */
	public void insertMapMerchant(String oldmerchantno, String newmerchantno) {
		String sql = "insert into MapMerchant (MERCHANTNO,NEWMERCHANTNO) values ('"
				+ oldmerchantno + "','" + newmerchantno + "')";
		Query query = _em.createNativeQuery(sql);
		query.executeUpdate();
	}

	/**
	 * 插入卡种类对应表
	 */
	public void insertMapCardClassic(String oldClassido, String newClassid) {
		String sql = "insert into MapCardClassic (BINDINGTYPENO,CARDCLASSICID) values ('"
				+ oldClassido + "','" + newClassid + "')";
		Query query = _em.createNativeQuery(sql);
		query.executeUpdate();

	}

	/**
	 * 插入销售机构对应表
	 */
	public void insertMapSaleBranch(String customid, String saleBranchNo) {
		String sql = "insert into MAPSALEBRANCH (CUSTOMID,SALEBRANCHNO) values ('"
				+ customid + "','" + saleBranchNo + "')";
		Query query = _em.createNativeQuery(sql);
		query.executeUpdate();

	}

	/**
	 * 插入发卡机构对应表
	 */
	public void insertMapIssueBranch(String branchNo, String issueBranchNo) {
		String sql = "insert into MapIssueBranch (BRANCHNO,ISSUEBRANCHNO) values ('"
				+ branchNo + "','" + issueBranchNo + "')";
		Query query = _em.createNativeQuery(sql);
		query.executeUpdate();
	}

	/**
	 * 插入用户对应表
	 */
	public void insertMapSysOperuser(String olduser, String newuser) {
		String sql = "insert into MapSysOperuser (OPERNO,OPERID) values ('"
				+ olduser + "','" + newuser + "')";
		Query query = _em.createNativeQuery(sql);
		query.executeUpdate();
	}

	/**
	 * 得到当前的数据迁移日期
	 */
	public Date getSortInfoDate() {

		try {
			String sql = "select Trandate+1 from MapIncome";
			Query query = _em.createNativeQuery(sql);
			Date d = (Date) query.getSingleResult();
			return d;
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 更新当前的数据迁移日期
	 */
	public void updateSortInfoDate(Date date) {
		String sql = "update MapIncome set trandate=?1";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, date);
		query.executeUpdate();
	}
	/**
	 * 插入MAPBATCHNOVALUE
	 */
	public void insertMapbatchnoValue(String branchno,int cardparvaue){
		String sql = "insert into MAPBATCHNOVALUE (BATCHNO,CARDPARVALUE) values ('"
			+ branchno + "'," + cardparvaue + ")";
	    Query query = _em.createNativeQuery(sql);
	    query.executeUpdate();
	}
	/**
	 *根据批次号查询卡面
	 */
	public int getMapbatchnoValueByBranchno(String branchno){
		if (PCPSUtil.isEmpty(branchno))
			return 0;
		try {
			String sql = "select CARDPARVALUE from MAPBATCHNOVALUE where BATCHNO=?1";
			Query query = _em.createNativeQuery(sql);
			query.setParameter(1, branchno);
			return Integer.parseInt(query.getSingleResult().toString());
		} catch (Exception e) {
			return 0;
		}
	}
}
